package com.it2297.model;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;
import java.util.*;

import com.it2297.main.App;

public class Product {
	private int productid;
	private String prodName;
	private String Desc;
	private double price;
	private int stock;
	private String status;
	private String category;

	public Product() {
	}

	public Product(String prodName, String Desc, double price, int stock,
			String status, String category) {
		// auto assign product id
		this.prodName = prodName;
		this.Desc = Desc;
		this.price = price;
		this.stock = stock;
		this.status = status;
		this.category = category;
	}

	public int getProductid() {
		return productid;
	}

	public void setProductid(int productid) {
		this.productid = productid;
	}

	public String getProdName() {
		return prodName;
	}

	public void setProdName(String prodName) {
		this.prodName = prodName;
	}

	public double getPrice() {
		return price;
	}

	public void setPrice(double price) {
		this.price = price;
	}

	public int getStock() {
		return stock;
	}

	public void setStock(int stock) {
		this.stock = stock;
	}

	public String getStatus() {
		return status;
	}

	public void setStatus(String status) {
		this.status = status;
	}

	public String getCategory() {
		return category;
	}

	public void setCategory(String category) {
		this.category = category;
	}

	public String getDesc() {
		return Desc;
	}

	public void setDesc(String desc) {
		Desc = desc;
	}
	
	public boolean isExistProduct(String name){
		boolean valid = false;
		
		try {
			App.mySqlConnection.open();
			String sql = "SELECT * FROM americano.product where prodname = ?";
			PreparedStatement ps = App.mySqlConnection.getConnection().prepareStatement(sql);
			ps.setString(1, name);
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				valid = true;
			}else{
				valid = false;
			}
		} catch (SQLException e) {
			valid = false;
			e.printStackTrace();
		}finally{
			App.mySqlConnection.close();
		}
		return valid;
	}
	public boolean isObsProduct(String name){
		boolean valid = false;
		
		try {
			App.mySqlConnection.open();
			String sql = "SELECT * FROM americano.product where prodname = ? AND status = ?";
			PreparedStatement ps = App.mySqlConnection.getConnection().prepareStatement(sql);
			ps.setString(1, name);
			ps.setString(2, "obs");
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				valid = true;
			}else{
				valid = false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			App.mySqlConnection.close();
		}
		return valid;
	}
	public int getQuantity(int productid){
		int id =0;
		try {
			App.mySqlConnection.open();
			String sql = "SELECT stock_count FROM americano.product where product_id=?";
			PreparedStatement ps = App.mySqlConnection.getConnection()
					.prepareStatement(sql);
			
			ps.setInt(1, productid);
			
			ResultSet rs = ps.executeQuery();
			
			if(rs.next()){
				id = rs.getInt("stock_count");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			App.mySqlConnection.close();
		}
		
		return id;
		
	}
	
	public void  updatedQuantity(int quantity, int productid){
		
		try {
			App.mySqlConnection.open();
			String sql = "UPDATE americano.product set stock_count=? where product_id=?";
			PreparedStatement ps = App.mySqlConnection.getConnection()
					.prepareStatement(sql);
			ps.setInt(1, quantity);
			ps.setInt(2, productid);
			
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			App.mySqlConnection.close();
		}
		
	}
	public Product getSingleProduct(String prodname) {
		try {
			App.mySqlConnection.open();
			String sql = "SELECT * FROM americano.product WHERE Prodname = ? AND status = 'ava'";
			PreparedStatement ps = App.mySqlConnection.getConnection().prepareStatement(sql);
			ps.setString(1, prodname);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				setAttributes(rs);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}
		return this;
	}
	
	public Product getunpaidProduct(int product_id) {
		try {
			App.mySqlConnection.open();
			String sql = "SELECT * FROM americano.product WHERE product_id = ?";
			PreparedStatement ps = App.mySqlConnection.getConnection().prepareStatement(sql);
			ps.setInt(1, product_id);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				setAttributes(rs);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}
		return this;
	}

	public int numOfITem() {
		Product product = new Product();

		ArrayList<Product> prod_arr = new ArrayList<Product>();
		prod_arr = product.getMultiple();
		int numOfProduct = prod_arr.size();

		return numOfProduct;
	}

	public ArrayList<Product> getMultiple() {
		ArrayList<Product> multi = new ArrayList<Product>();
		try {
			App.mySqlConnection.open();
			String sql = "SELECT * FROM americano.product where status = 'ava'";
			PreparedStatement ps = App.mySqlConnection.getConnection()
					.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Product product = new Product();
				product.setAttributes(rs);
				multi.add(product);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}
		return multi;
	}
	
	public Product getSelected(int id){
		Product product = new Product();
		try {
			App.mySqlConnection.open();
			String sql = "SELECT * FROM americano.product where status = 'ava' and product_id =?";
			PreparedStatement ps = App.mySqlConnection.getConnection().prepareStatement(sql);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				product.setAttributes(rs);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}
		return product;
	}

	public static ArrayList<Product> getMultipleItem(int index, int count) {
		ArrayList<Product> multi = new ArrayList<Product>();
		try {
			App.mySqlConnection.open();
			String sql = "SELECT * FROM americano.product where status = 'ava' order by product_id LIMIT ?, ? ";
			PreparedStatement ps = App.mySqlConnection.getConnection()
					.prepareStatement(sql);
			ps.setInt(1, index);
			ps.setInt(2, count);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Product product = new Product();
				product.setAttributes(rs);
				multi.add(product);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}
		return multi;
	}
	
	
	public static ArrayList<Product> getMultipleDeletedItem(int index, int count) {
		ArrayList<Product> multi = new ArrayList<Product>();
		try {
			App.mySqlConnection.open();
			String sql = "SELECT * FROM americano.product where status = 'obs' LIMIT ?, ? ";
			PreparedStatement ps = App.mySqlConnection.getConnection()
					.prepareStatement(sql);
			ps.setInt(1, index);
			ps.setInt(2, count);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Product product = new Product();
				product.setAttributes(rs);
				multi.add(product);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}
		return multi;
	}

	public ArrayList<String> getType() {
		ArrayList<String> arr_pro = new ArrayList<String>();
		try {
			App.mySqlConnection.open();
			String sql = "select category from americano.category";
			PreparedStatement ps = App.mySqlConnection.getConnection()
					.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				arr_pro.add(rs.getString("category"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}
		return arr_pro;
	}

	public void addProduct(Product product) {

		try {
			App.mySqlConnection.open();
			String sql = "INSERT INTO americano.product (prodname, description, price, stock_count, status, category)";
			sql += "VALUES(?,?,?,?,?,?)";

			PreparedStatement ps = App.mySqlConnection.getConnection()
					.prepareStatement(sql);

			ps.setString(1, product.getProdName());
			ps.setString(2, product.getDesc());
			ps.setDouble(3, product.getPrice());
			ps.setInt(4, product.getStock());
			ps.setString(5, product.getStatus());
			ps.setString(6, product.getCategory());

			ps.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}

	}

	public boolean delProduct(Product product) {
		boolean valid =false;
		try {
			App.mySqlConnection.open();
			String sql = "UPDATE americano.product set status='obs' where prodname = ?";
			PreparedStatement ps = App.mySqlConnection.getConnection().prepareStatement(sql);
			valid = true;
			ps.setString(1, product.getProdName());
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			valid = false;
			e.printStackTrace();
		}finally{
			App.mySqlConnection.close();
		}
		return valid;
	}
	
	public boolean retrieveProduct(Product product) {
		boolean valid =false;
		try {
			App.mySqlConnection.open();
			String sql = "UPDATE americano.product set status='ava' where prodname = ?";
			PreparedStatement ps = App.mySqlConnection.getConnection().prepareStatement(sql);
			valid = true;
			ps.setString(1, product.getProdName());
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			valid = false;
			e.printStackTrace();
		}finally{
			App.mySqlConnection.close();
		}
		return valid;
	}

	public boolean updateProduct(Product product) {
			boolean valid = false;
		try {
			App.mySqlConnection.open();
			String sql = "UPDATE americano.product set description =? , price =?, stock_count =?, status =?, category =? where prodname = ?";
			PreparedStatement ps = App.mySqlConnection.getConnection()
					.prepareStatement(sql);
			ps.setString(1, product.getDesc());
			ps.setDouble(2, product.getPrice());
			ps.setInt(3, product.getStock());
			ps.setString(4, product.getStatus());
			ps.setString(5, product.getCategory());
			ps.setString(6, product.getProdName());

			ps.executeUpdate();
			valid = true;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}
		return valid;

	}

	public static int getRecordNum() {
		int recordNum = 0;

		try {
			App.mySqlConnection.open();
			String sql = "SELECT COUNT(*) FROM americano.product WHERE status='ava'";
			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement(sql);
			ResultSet resultSet = preparedStatement.executeQuery();

			if (resultSet.next()) {
				recordNum = resultSet.getInt(1);
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}

		return recordNum;
	}
	
	public static int getUnavaRecordNum(){
		int recordNum = 0;

		try {
			App.mySqlConnection.open();
			String sql = "SELECT COUNT(*) FROM americano.product WHERE status='obs'";
			PreparedStatement preparedStatement = App.mySqlConnection.getConnection().prepareStatement(sql);
			ResultSet resultSet = preparedStatement.executeQuery();

			if (resultSet.next()) {
				recordNum = resultSet.getInt(1);
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			App.mySqlConnection.close();
		}

		return recordNum;
	}

	private void setAttributes(ResultSet rs) {

		try {
			this.setProdName(rs.getString("prodName"));
			this.setProductid(rs.getInt("product_id"));
			this.setDesc(rs.getString("description"));
			this.setPrice(rs.getDouble("price"));
			this.setStock(rs.getInt("stock_count"));
			this.setStatus(rs.getString("status"));
			this.setCategory(rs.getString("category"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
